﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.IO;
using System.Data;
using System.Threading;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;
using Microsoft.Win32;

namespace WPFNpoi
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private List<OA> _oaList = new List<OA>();
        public MainWindow()
        {
            InitializeComponent();
            _oaList.Add(new OA() { ID = Guid.NewGuid().ToString(), Code = "AO_01", Name = "办公系统", CreateTime = DateTime.Now.AddHours(-1) });
            _oaList.Add(new OA() { ID = Guid.NewGuid().ToString(), Code = "AO_02", Name = "基础系统", CreateTime = DateTime.Now });
            _oaList.Add(new OA() { ID = Guid.NewGuid().ToString(), Code = "AO_03", Name = "资料系统", CreateTime = DateTime.Now.AddHours(+1) });
            _oaList.Add(new OA() { ID = Guid.NewGuid().ToString(), Code = "AO_04", Name = "OA系统", CreateTime = DateTime.Now });
            _oaList.Add(new OA() { ID = Guid.NewGuid().ToString(), Code = "AO_05", Name = "财务系统", CreateTime = DateTime.Now });
        }
        //TEST
        private void button4_Click(object sender, RoutedEventArgs e)
        {
            //创建工作薄
            XSSFWorkbook wk = new XSSFWorkbook();
            //创建一个名称为mySheet的表
            ISheet tb = wk.CreateSheet("mySheet");
            //创建一行，此行为第二行
            IRow row = tb.CreateRow(1);
            for (int i = 0; i < 20; i++)
            {
                ICell cell = row.CreateCell(i);  //在第二行中创建单元格
                cell.SetCellValue(i);//循环往第二行的单元格中添加数据
            }
            using (FileStream fs = File.OpenWrite(@"G:/Desktop/myxlsExcel.xlsx")) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                wk.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
                MessageBox.Show("提示：创建成功！");
            }

        }

        private void button3_Click(object sender, RoutedEventArgs e)
        {
            //定位
            string str = "用户:#Code";
            string oldstring = "#Code";
            string newstring = "TKW";

            string value = str.Replace(oldstring, newstring);
        }
        //导出
        private void button2_Click(object sender, RoutedEventArgs e)
        {
            //导出：选择模版、导出
            //1、获取模版 2、获取数据 、3计算表格
            #region 约定数据
            List<Promise> promiselist = new List<Promise>();//约定
            promiselist.Add(new Promise() { PromiseValue = "##CurrentUserCode", Value = "TKW" });
            promiselist.Add(new Promise() { PromiseValue = "##CurrentUserName", Value = "唐开文" });
            promiselist.Add(new Promise() { PromiseValue = "##CurrentOrganName", Value = "中环建设有限公司" });
            promiselist.Add(new Promise() { PromiseValue = "##CurrentOrganCode", Value = "ZHJS" });
            #endregion

            #region 标题头
            List<HeaderProperty> headerplist = new List<HeaderProperty>();//约定
            headerplist.Add(new HeaderProperty() { Code = "#OA.Code", Name = "代码" });
            headerplist.Add(new HeaderProperty() { Code = "#OA.Name", Name = "名字" });
            headerplist.Add(new HeaderProperty() { Code = "#OA.CreateTime", Name = "创建时间" });
            headerplist.Add(new HeaderProperty() { Code = "#OA.Content", Name = "内容" });
            headerplist.Add(new HeaderProperty() { Code = "#OA.Type", Name = "类型" });

            #endregion


            ExcelCommon.ExportExcel(path, promiselist.ToArray(), headerplist.ToArray(), _oaList.ToArray());
            MessageBox.Show("提示：创建成功！");
        }



        private void button1_Click(object sender, RoutedEventArgs e)
        {
            DataTable dt = null;
            ////导入： 设置映射关系
            string path = "";
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel(2007)|*.xlsx|Excel(97-03)|*.xls";
            bool? b = ofd.ShowDialog();
            if (b.Value == true)
            {
                path = ofd.FileName;
            }

            #region 映射关系

            List<string> headlist = ExcelCommon.GetHeader(path, 0);//获取所有的列头
            List<HeaderProperty> headerProMapping = new List<HeaderProperty>();
            headerProMapping.Add(new HeaderProperty() { Code = "#OA.Code", Name = headlist[0] });
            headerProMapping.Add(new HeaderProperty() { Code = "#OA.Name", Name = headlist[1] });
            headerProMapping.Add(new HeaderProperty() { Code = "#OA.CreateTime", Name = headlist[2] });
            headerProMapping.Add(new HeaderProperty() { Code = "#OA.Content", Name = headlist[3] });
            headerProMapping.Add(new HeaderProperty() { Code = "#OA.Type", Name = headlist[4] });

            #endregion

            dt = ExcelCommon.ImportExcel(path, 0, headerProMapping.ToArray());
            foreach (DataRow dr in dt.Rows)
            {
                string code = dr["#OA.Code"].ToString();
            }

        }
        string path = @"G:/Desktop/ExcelTemplate.xlsx";

        //读取
        private void button5_Click(object sender, RoutedEventArgs e)
        {
            DataTable dt = ExcelCommon.GetDataTable(path, 0, true);

        }

        //插入
        private void button6_Click(object sender, RoutedEventArgs e)
        {
            //XSSFWorkbook workbook = ExcelCommon.GetXSSFWorkBook(path);
            //ISheet sheet = workbook.GetSheetAt(0);
            //ExcelCommon.InsertRow(sheet, 1, _oaList.ToArray());
            //FileStream stm = new FileStream(path, FileMode.Create, FileAccess.Write);

            ////using (FileStream stm = File.FileStream(path, FileMode.Create, FileAccess.Write))
            ////{
            //workbook.Write(stm);
            //stm.Close();
            //MessageBox.Show("提示：创建成功！");
            // }  
            try
            {
                using (FileStream file = File.OpenRead(path))
                {
                    XSSFWorkbook workbook = new XSSFWorkbook(file);
                    ISheet sheet = workbook.GetSheetAt(0);

                    int[] rowcol = ExcelCommon.GetRowColCoordinate(sheet, "##CurrentName");
                    sheet.GetRow(rowcol[0]).GetCell(rowcol[1]).SetCellValue("当前用户：唐开文");


                    sheet.GetRow(0).GetCell(0).SetCellValue("代码");
                    sheet.GetRow(0).GetCell(1).SetCellValue("名字");
                    sheet.GetRow(0).GetCell(2).SetCellValue("创建时间");
                    sheet.GetRow(0).GetCell(3).SetCellValue("内容");
                    sheet.GetRow(0).GetCell(4).SetCellValue("类型");

                    sheet.GetRow(1).GetCell(0).SetCellValue("TKW");
                    sheet.GetRow(1).GetCell(1).SetCellValue("唐凯文");
                    sheet.GetRow(1).GetCell(2).SetCellValue("2012-2-1");
                    sheet.GetRow(1).GetCell(3).SetCellValue("OCntent");
                    sheet.GetRow(1).GetCell(4).SetCellValue("中文");
                    //样式
                    ICellStyle style = sheet.GetRow(1).GetCell(0).CellStyle;
                    sheet.ShiftRows(1, 4, 2);

                    IRow row = sheet.CreateRow(2);
                    for (int i = 0; i < 5; i++)
                    {
                        ICell cell = row.CreateCell(i);
                        cell.SetCellValue("TKW");
                        cell.CellStyle = style;//复制样式
                    }
                    //sheet.GetRow(19).GetCell(0).SetCellValue("TKW");
                    //sheet.GetRow(19).GetCell(1).SetCellValue("唐凯文");
                    //sheet.GetRow(19).GetCell(2).SetCellValue("2012-2-1");
                    //sheet.GetRow(19).GetCell(3).SetCellValue("OCntent");
                    //sheet.GetRow(19).GetCell(4).SetCellValue("中文");
                    FileStream stm = new FileStream(@"G:/Desktop/NewExcel.xlsx", FileMode.Create, FileAccess.Write);
                    workbook.Write(stm);
                    stm.Close();
                    MessageBox.Show("提示：创建成功！");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }
}
